﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ZG_member.BLL;
using System.Configuration;
using System.Data.OleDb;

namespace ZG_member
{
    public partial class installgift : Form
    {
        public installgift()
        {
            InitializeComponent();
            skinEngine1.SkinFile = Application.StartupPath + ConfigurationManager.AppSettings["Skin"]; 
        }

        private void installgift_Load(object sender, EventArgs e)
        {
            this.Opacity = 0;
            this.timer1.Start();
            gifts gif = new gifts();
            DataTable dt = gif.GetList("").Tables[0];
            DgvGifts.DataSource = dt;

            dgvdaochu.DataSource = dt;
        }

        private void btnproductDel_Click(object sender, EventArgs e)
        {
             DialogResult dr = MessageBox.Show("确定删除？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
             if (dr == DialogResult.OK)
             {
                 gifts gif = new gifts();
                 gif.Delete(int.Parse(DgvGifts.CurrentRow.Cells[0].Value.ToString()));
                 DataTable dt = gif.GetList("").Tables[0];
                 DgvGifts.DataSource = dt;
             }
        }

        private void btnProduceAdd_Click(object sender, EventArgs e)
        {
            GiftSetForm gs = new GiftSetForm("0");
            gs.Text = "新增礼品";
            gs.ShowDialog();
            
            gifts gif = new gifts();
            DataTable dt = gif.GetList("").Tables[0];
            DgvGifts.DataSource = dt;
        }

        private void btnProductUpt_Click(object sender, EventArgs e)
        {
            GiftSetForm gs = new GiftSetForm(DgvGifts.CurrentRow.Cells[0].Value.ToString());
            gs.Text = "修改礼品";
            gs.ShowDialog();
            
            gifts gif = new gifts();
            DataTable dt = gif.GetList("").Tables[0];
            DgvGifts.DataSource = dt;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            //timer开启
            this.Left += 2;
            this.Opacity += 0.1;
            if (Opacity == 1)
            {
                this.timer1.Stop();

            }
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            //timer关闭
            this.Top = this.Top + 10;
            this.Opacity = this.Opacity - 0.06;
            if (Opacity <= 0)
            {
                this.timer2.Stop();
                this.Close();
            }
        }
        int flag = 0;

        private void button2_Click(object sender, EventArgs e)
        {
            //测试，将excel中的sheet1导入到sqlserver中 
            string connString = ConfigurationManager.AppSettings["ConnectionString"];
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                TransferData(fd.FileName, "sheet1", connString);
            }
            gifts gif = new gifts();
            DataTable dt = gif.GetList("").Tables[0];
            DgvGifts.DataSource = dt;
        }
        public void TransferData(string excelFile, string sheetName, string connectionString)
        {
            ZG_member.BLL.gifts adv = new ZG_member.BLL.gifts();
            ZG_member.Model.gifts advMode = new ZG_member.Model.gifts();
            DataTable advTB = adv.GetList("").Tables[0];
            DataSet ds = new DataSet();


            try
            {
                try
                {
                    //获取全部数据   
                    string strConn = "Provider=Microsoft.ACE.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    strExcel = string.Format("select * from [{0}$]", sheetName);
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds, sheetName);
                    bool bl = true;
                    //如果目标表不存在则创建   
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string strSql = "insert into gifts values(";
                        //strSql += string.Format("{0} varchar(255),", c.ColumnName);

                        //if (advTB.Columns[i + 1].ToString() == ds.Tables[0].Columns[i].ToString())
                        //{
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {

                            strSql += "'" + ds.Tables[0].Rows[i][j].ToString() + "',";

                        }
                        //}
                        //else
                        //{
                        //    bl = false;
                        //    if (bl == false)
                        //    {
                        //        MessageBox.Show("Excel与数据库标题不一致");
                        //        return;
                        //    }
                        //    break;
                        //}

                        strSql = strSql.Trim(',') + ")";


                        using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                        {
                            sqlconn.Open();
                            System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                            command.CommandText = strSql;
                            command.ExecuteNonQuery();
                            sqlconn.Close();

                        }

                    }
                    // 用bcp导入数据   
                    //using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                    //{
                    //    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    //    bcp.BatchSize = 100;//每次传输的行数   
                    //    bcp.NotifyAfter = 100;//进度提示的行数   
                    //    bcp.DestinationTableName = "goods";//目标表   
                    //    bcp.WriteToServer(ds.Tables[0]);
                    //}
                }
                catch (Exception ex)
                {
                    //获取全部数据   
                    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    strExcel = string.Format("select * from [{0}$]", sheetName);
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds, sheetName);
                    bool bl = true;
                    //如果目标表不存在则创建   
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string strSql = "insert into gifts values(";
                        //strSql += string.Format("{0} varchar(255),", c.ColumnName);

                        //if (advTB.Columns[i + 1].ToString() == ds.Tables[0].Columns[i].ToString())
                        //{
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {

                            strSql += "'" + ds.Tables[0].Rows[i][j].ToString() + "',";

                        }
                        //}
                        //else
                        //{
                        //    bl = false;
                        //    if (bl == false)
                        //    {
                        //        MessageBox.Show("Excel与数据库标题不一致");
                        //        return;
                        //    }
                        //    break;
                        //}

                        strSql = strSql.Trim(',') + ")";


                        using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                        {
                            sqlconn.Open();
                            System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                            command.CommandText = strSql;
                            command.ExecuteNonQuery();
                            sqlconn.Close();

                        }

                    }


                    
                }
                goods good = new goods();
                DataTable dtt = good.GetList("").Tables[0];
                MessageBox.Show("导入成功");
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.Message);
                MessageBox.Show("请确保Excel格式内容正确!");
                return;
            }
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExportExcel("", dgvdaochu);
        }
        public void ExportExcel(string fileName, DataGridView myDGV)
        {
            string saveFileName = "";
            //bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消 
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Office.Excel组件");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            int colIndex = 0;
            //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                if (myDGV.Columns[i].Visible)
                {
                    colIndex++;
                    worksheet.Cells[1, colIndex] = myDGV.Columns[i].HeaderText;
                    //  worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
                }
            }
            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                colIndex = 0;
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    if (myDGV.Columns[i].Visible)
                    {
                        colIndex++;
                        worksheet.Cells[r + 2, colIndex] = myDGV.Rows[r].Cells[i].Value;
                    }
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);
                }

            }
            xlApp.Quit();
            GC.Collect();//强行销毁 
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

            MessageBox.Show(fileName + "导出成功", "系统提示", MessageBoxButtons.OK);
        }
    }

}
